import mysql.connector
import pandas as pd
import os
from data_analyzer import clean_data

BASE_PATH = "./zhaopin_system/data/"
DB_CONFIG = {
    "host": "localhost",
    "user": "root",
    "passwd": "Syj240986482",
    "database": "zhaopindb"  # 创建数据库时会临时移除该参数
}


def create_database():
    """创建数据库及表"""
    # 连接到 MySQL 服务器（不指定数据库）
    config = DB_CONFIG.copy()
    config.pop("database")  # 移除 database 参数
    conn = mysql.connector.connect(**config)
    cursor = conn.cursor()

    # 创建数据库（若不存在）
    cursor.execute("CREATE DATABASE IF NOT EXISTS zhaopindb")
    print("[SUCCESS] 数据库创建完成")

    # 连接到指定数据库
    conn = mysql.connector.connect(**DB_CONFIG)
    cursor = conn.cursor()

    # 创建职位表
    create_table_sql = """
    CREATE TABLE IF NOT EXISTS jobs (
        id INT AUTO_INCREMENT PRIMARY KEY,
        company_name VARCHAR(255) NOT NULL,
        job_title VARCHAR(255) NOT NULL,
        low_salary INT,
        high_salary INT,
        average_salary DECIMAL(10, 2),
        company_size VARCHAR(50),
        work_city VARCHAR(100),
        publish_time DATE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    """
    cursor.execute(create_table_sql)
    conn.commit()
    cursor.close()
    conn.close()
    print("[SUCCESS] 表创建完成")


def save_to_mysql(df):
    """将DataFrame数据插入数据库"""
    conn = mysql.connector.connect(**DB_CONFIG)
    cursor = conn.cursor()

    # 插入数据模板
    insert_sql = """
    INSERT INTO jobs (company_name, job_title, low_salary, high_salary, 
                      average_salary, company_size, work_city, publish_time)
    VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
    """

    # 批量插入（修正列名映射）
    values = df[["companyName", "job_title", "low_salary", "high_salary",
                 "average_salary", "companySize", "workCity", "publish_time"]].values.tolist()
    cursor.executemany(insert_sql, values)
    conn.commit()
    print(f"[SUCCESS] 成功插入{cursor.rowcount}条数据")
    cursor.close()
    conn.close()


# 交互式数据存储
def interactive_save_to_db():
    """交互式选择页码并存储数据到数据库"""
    create_database()

    # 获取可用页码列表
    available_pages = []
    for file in os.listdir(BASE_PATH):
        if file.startswith("raw_data_page") and file.endswith(".json"):
            try:
                page_num = int(file.split("_")[-1].split(".")[0].replace("page", ""))
                available_pages.append(page_num)
            except:
                continue

    if not available_pages:
        print(f"错误：{BASE_PATH} 目录下未找到数据文件！")
        return

    print(f"找到以下页码的数据：{sorted(available_pages)}")

    # 用户选择页码
    while True:
        try:
            pages_input = input(f"请输入要存储的页码范围（例如 1-3）：")
            if "-" in pages_input:
                start, end = map(int, pages_input.split("-"))
                pages = list(range(start, end + 1))
            else:
                pages = [int(pages_input)]

            # 检查页码有效性
            invalid_pages = [p for p in pages if p not in available_pages]
            if invalid_pages:
                print(f"错误：页码 {invalid_pages} 不存在！")
                continue

            break
        except ValueError:
            print("请输入有效的页码范围！")

    # 处理并存储选中的页码数据
    for page in pages:
        try:
            raw_file = f"{BASE_PATH}raw_data_page{page}.json"
            cleaned_df = clean_data(raw_file)
            save_to_mysql(cleaned_df)
            print(f"[SUCCESS] 第{page}页数据已成功存入数据库")
        except Exception as e:
            print(f"[ERROR] 处理第{page}页数据时出错：{str(e)}")


if __name__ == "__main__":
    interactive_save_to_db()